﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using NTTDATA.BEENChina.ResultCertification.Result;

namespace NTTDATA.BEENChina.ResultCertification.DataStore
{
    public class MakeJobClientAccess :IMakeJob
    {
        private static string ConnectionString
        {
            get
            {
                return System.Configuration.ConfigurationManager.AppSettings["ResultCertificationDB"];
            }
        }  
        
        public List<string> GetDistinctOridReceiveDates()
        {
            string sql = "  SELECT  DISTINCT  CAST( B.OridReceiveDate as DATE) [OridReceiveDate] " +
                         "  FROM [ChildImages] A LEFT JOIN Orders B ON A.OrderID = B.OrderID " +
                         "  WHERE  [VerfityJobID] is null";

            List<string> oridReceiveDate = null;

            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql))
                {
                    while (reader.Read())
                    {
                        if (oridReceiveDate == null)
                            oridReceiveDate = new List<string>();

                        oridReceiveDate.Add(reader.GetDateTime(0).ToString("yyyy-MM-dd"));
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("EntryClientAccess.GetVerfityDates", ex);
            }

            return oridReceiveDate;
        }

        public List<string> GetDistinctVerfityItemIds(string oridReceiveDate)
        {
            string sql =    "    SELECT  DISTINCT  A.VerfityItemID [VerfityItemID] "+
                            "    FROM [ChildImages] A LEFT JOIN Orders B ON A.OrderID = B.OrderID " +
                            "    WHERE  [VerfityJobID] is null AND cast( B.OridReceiveDate as date) =  @OridReceiveDate ";
           
            List<string> verfityIds = null;

            SqlParameter[] sqlParameters =
                {
                    new SqlParameter("@OridReceiveDate", oridReceiveDate) 

                };

            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
                {
                    while (reader.Read())
                    {
                        if (verfityIds == null)
                            verfityIds = new List<string>();

                        verfityIds.Add(reader["VerfityItemID"].ToString());
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("EntryClientAccess.GetDistinctVerfityItemIds", ex);
            }

            return verfityIds;
        }

        //public List<string> GetChildImageSeqnos(string oridReceiveDate, string verfityItemId)
        public List<ChildImage> GetChildImageSeqnos(string oridReceiveDate, string verfityItemId)
        {
            //string sql = "  SELECT  ID "+
            //            "   FROM [ChildImages] A LEFT JOIN Orders B ON A.OrderID = B.OrderID "+
            //            "   WHERE  [VerfityJobID] IS NULL AND " +
            //             "       cast( B.OridReceiveDate as date) = @OridReceiveDate AND " +
            //             "      A.VerfityItemID  = @VerfityItemID ";

            string sql = "  SELECT  A.ID [ID], " +
                         "          A.CategoryID [CategoryID]," +
                         "          A.CategoryName [CategoryName] " +
                        "   FROM [ChildImages] A LEFT JOIN Orders B ON A.OrderID = B.OrderID " +
                        "   WHERE  [VerfityJobID] IS NULL AND " +
                         "       cast( B.OridReceiveDate as date) = @OridReceiveDate AND " +
                         "      A.VerfityItemID  = @VerfityItemID ";

            //List<string> childImageSeqnos = null;
            List<ChildImage> childImageSeqnos = null;

            SqlParameter[] sqlParameters =
                {
                    new SqlParameter("@OridReceiveDate", oridReceiveDate) ,
                     new SqlParameter("@VerfityItemID", verfityItemId) 
                };

            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
                {
                    while (reader.Read())
                    {
                        if (childImageSeqnos == null)
                            //childImageSeqnos = new List<string>();
                            childImageSeqnos = new List<ChildImage>();
                            
                        //childImageSeqnos.Add(reader["ID"].ToString());
                        childImageSeqnos.Add(new ChildImage
                        {
                            ImageId = reader["ID"].ToString(),
                            CategoryId = reader["CategoryID"].ToString(),
                            CategoryName = reader["CategoryName"].ToString()
                        });
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("EntryClientAccess.GetChildImageSeqnos", ex);
            }

            return childImageSeqnos;
        }
        
        public int MakeVerfityJob(string verfityJobId, string verfityItemId, string oridReceiveDate, int imageCount,string categoryId,string categoryName)
        {
            int effactRow = 0;
            string sql =" INSERT INTO [VerfityJob]" +
                        " ( [VerfityJobID]	,[VerfityItemID]	,[OridReceiveDate]	,[ImageCount],[CategoryId],[CategoryName] )" +
                        " VALUES (@VerfityJobID	,@VerfityItemID	,@oridReceiveDate	,@ImageCount,@CategoryId,@CategoryName ) ";

            SqlParameter[] sqlParameters =
                {
                    new SqlParameter("@VerfityJobID", verfityJobId),
                    new SqlParameter("@VerfityItemID", verfityItemId),
                    new SqlParameter("@oridReceiveDate", oridReceiveDate),
                    new SqlParameter("@ImageCount", imageCount),
                    new SqlParameter("@CategoryId",categoryId),
                    new SqlParameter("@CategoryName",categoryName) 

                };

            try
            {
                 effactRow = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);

                
            }
            catch (Exception ex)
            {

                Log.Console.Error("EntryClientAccess.VerfityOperator", ex);

            }


            return effactRow;
        }

        public int UpdateChildImageVerfityJobId(string verfityJobId, List<string> seqnos)
        {
            int effactRow = 0;

            foreach (string seqno in seqnos)
            {
                string sql = "UPDATE [ChildImages]   SET [VerfityJobID] = @VerfityJobID      WHERE ID = @id";

                SqlParameter[] sqlParameters =
                {
                    new SqlParameter("@id", seqno),
                    new SqlParameter("@VerfityJobID", verfityJobId), 

                };
                try
                {
                    effactRow += SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);


                }
                catch (Exception ex)
                {

                    Log.Console.Error("EntryClientAccess.VerfityOperator", ex);

                }

            }

            return effactRow;
        } 
     
    }
}
